package com.zx.cloud.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.zx.cloud.def.ColumnMetadata;
import com.zx.cloud.def.LinkMetadata;
import com.zx.cloud.def.PKColumnMetadata;
import com.zx.cloud.def.TableMetadata;
import com.zx.cloud.utils.ConfigUtils.Cfg;

public class DBUtils {

	public static List<String> tableRemovePrefixChars = new ArrayList<String>();
	public static List<String> jspRemovePrefixChars = new ArrayList<String>();
	public static List<String> genertorTableNames = new ArrayList<String>();
	public static Map<String,String> genertorTableChinseNames = new HashMap<String,String>();
	
	public static final String CONFIG_FILE = "config.properties";

	public static boolean genertor_dao = false;
	public static boolean genertor_model = false;
	public static boolean genertor_service = false;
	public static boolean genertor_controller = false;
	public static boolean genertor_jsp = false;
	public static boolean genertor_mapper = false;
	public static boolean genertor_insert_sql = false;
	
	public static boolean app_genertor_dao = false;
	public static boolean app_genertor_mapper = false;
	public static boolean app_genertor_model = false;
	
	public static String html_header_title;
	public static String html_header_keywords;
	public static String html_header_description;
	
	public static final String KEY_DRIVER = "jdbc.driver";
	public static final String KEY_URL = "jdbc.url";
	public static final String KEY_USERNAME = "jdbc.username";
	public static final String KEY_PASSWORD = "jdbc.password";

	private static  String DRIVER;
	private static  String URL;
	private static  String USERNAME ;
	private static  String PASSWORD ;
	
	public static void initProperties() {
		Cfg cfg = ConfigUtils.getCfg(CONFIG_FILE);
		DRIVER= cfg.get(KEY_DRIVER);
		URL= cfg.get(KEY_URL);
		USERNAME= cfg.get(KEY_USERNAME);
		PASSWORD = cfg.get(KEY_PASSWORD);
		
		String genertor_dao_ = cfg.get("genertor.dao");
		String genertor_model_ = cfg.get("genertor.model");
		String genertor_service_ = cfg.get("genertor.service");
		String genertor_controller_ = cfg.get("genertor.controller");
		String genertor_jsp_ = cfg.get("genertor.jsp");
		String genertor_mapper_ = cfg.get("genertor.mapper");
		String genertor_insert_sql_ = cfg.get("genertor.insert.sql");
		
		String app_genertor_dao_ = cfg.get("app.genertor.dao");
		String app_genertor_mapper_ = cfg.get("app.genertor.mapper");
		String app_genertor_model_ = cfg.get("app.genertor.model");
		
		html_header_title = cfg.get("html.header.title");
		html_header_keywords = cfg.get("html.header.keywords");
		html_header_description= cfg.get("html.header.description");
		
		genertor_dao=Boolean.parseBoolean(genertor_dao_);
		genertor_model=Boolean.parseBoolean(genertor_model_);
		genertor_service=Boolean.parseBoolean(genertor_service_);
		genertor_controller=Boolean.parseBoolean(genertor_controller_);
		genertor_jsp=Boolean.parseBoolean(genertor_jsp_);
		genertor_mapper=Boolean.parseBoolean(genertor_mapper_);
		genertor_insert_sql=Boolean.parseBoolean(genertor_insert_sql_);
		
		app_genertor_dao=Boolean.parseBoolean(app_genertor_dao_);
		app_genertor_mapper=Boolean.parseBoolean(app_genertor_mapper_);
		app_genertor_model=Boolean.parseBoolean(app_genertor_model_);
		
		
		String prefixList = cfg.get("table.remove.prefix.chars");
		if (StringUtils.isNotEmpty(prefixList)) {
			String[] strings = prefixList.split(";+");
			if (strings != null && strings.length > 0) {
				for (String prefix : strings) {
					tableRemovePrefixChars.add(prefix.toLowerCase());
				}
			}
		}
		
		String jspPrefixList = cfg.get("jsp.remove.prefix.chars");
		if (StringUtils.isNotEmpty(jspPrefixList)) {
			String[] strings = jspPrefixList.split(";+");
			if (strings != null && strings.length > 0) {
				for (String prefix : strings) {
					jspRemovePrefixChars.add(prefix.toLowerCase());
				}
			}
		}
		
		String genertorTableNames_ = cfg.get("genertor.table.names");
		if (StringUtils.isNotEmpty(genertorTableNames_)) {
			String[] strings = genertorTableNames_.split(";+");
			if (strings != null && strings.length > 0) {
				for (String prefix : strings) {
					System.out.println("-----"+prefix);
					genertorTableNames.add(prefix);
				}
			}
		}
		
		String genertorTableChinseNames_ = cfg.get("genertor.table.chinse.names");
		if (StringUtils.isNotEmpty(genertorTableChinseNames_)) {
			String[] strings = genertorTableChinseNames_.split(";+");
			if (strings != null && strings.length > 0) {
				for (String prefix : strings) {
					if(StringUtils.isNotEmpty(prefix)){
						String[] tableAndName = prefix.split("[.]");
						if (tableAndName != null && tableAndName.length > 0) {
							DBUtils.genertorTableChinseNames.put(tableAndName[0], tableAndName[1]);
						}
					}
				}
			}
		}
		
		System.out.println(String.format("  加载配置项driver  ：%s", DRIVER));
		System.out.println(String.format("  加载配置项url     ：%s", URL));
		System.out.println(String.format("  加载配置项username：%s", USERNAME));
		System.out.println(String.format("  加载配置项password：%s", PASSWORD));
	}
	
	public static Connection getConn() {
		System.out.println("初始化数据库【开始】");
		Connection conn = null;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(
					String.format("[JDBC驱动加载错误][%s]", DRIVER), e);
		} catch (SQLException e) {
			throw new RuntimeException(String.format("[数据库连接失败]"), e);
		}
		System.out.println("初始化数据库【完成】！");
		return conn;
	}

	public static DatabaseMetaData getDatabaseMetaData() {
		Connection conn= DBUtils.getConn();
		DatabaseMetaData dbmd = null;
		ResultSet rs = null;
		try {
			dbmd = conn.getMetaData();
			rs = dbmd.getTypeInfo();
			while (rs.next()) {
				System.out.println(String.format("类型名称【%s】 JavaType【%s】最大精度【%s】",
						rs.getString(1),
						SqlTypeUtils.decodeToJavaType(rs.getInt(2)),
						rs.getString(3)));
			}
			String dbType = dbmd.getDatabaseProductName();
			String dbVersion = dbmd.getDatabaseProductVersion();
			String driverName = dbmd.getDriverName();
			String driverVersion = dbmd.getDriverVersion();
			System.out.println(String.format(
					"数据库类型【%s】数据库版本【%s】数据库驱动名称【%s】数据库驱动程序版本【%s】", dbType,
					dbVersion, driverName, driverVersion));
		} catch (SQLException e) {
			throw new RuntimeException("获取元数据出错", e);
		}
		return dbmd;
	}

	public static void loadMetadata(DatabaseMetaData dbmd) {
		String[] types = { "TABLE" };
		ResultSet rs = null;
		try {
			rs = dbmd.getTables(null, null, null, types);
			while (rs.next()) {
				solveTable(rs);
			}
			rs = dbmd.getColumns(null, null, null, null);
			while (rs.next()) {
				solveColumn(rs);
			}
			for (TableMetadata table : TableMetadata.getAllTables().values()) {
				rs = dbmd.getPrimaryKeys(null, null, table.getTableName());
				while (rs.next()) {
					solvePrimaryKey(rs);
				}
				rs = dbmd.getImportedKeys(null, null, table.getTableName());
				while (rs.next()) {
					solveForeignKey(rs);
				}
			}
		} catch (SQLException e) {
			throw new RuntimeException("获取表信息出错", e);
		}
	}

	private static void solveForeignKey(ResultSet rs) throws SQLException {
		String targetTableName = rs.getString("PKTABLE_NAME");
		String targetColumnName = rs.getString("PKCOLUMN_NAME");
		String tableName = rs.getString("FKTABLE_NAME");
		String columnName = rs.getString("FKCOLUMN_NAME");
		ColumnMetadata fromColumn = TableMetadata.find(tableName).getColumn(
				columnName);
		ColumnMetadata toColumn = TableMetadata.find(targetTableName)
				.getColumn(targetColumnName);
		LinkMetadata link = new LinkMetadata(fromColumn, toColumn);
		TableMetadata.find(fromColumn.getTableName()).addLink(link);
		TableMetadata.find(toColumn.getTableName()).addLinkBy(link);
		System.out.println(String.format("  表【%s】中的列【%s】引用表【%s】的列【%s】", tableName,
				columnName, targetTableName, targetColumnName));
	}

	private static void solvePrimaryKey(ResultSet rs) throws SQLException {
		String tableName = rs.getString("TABLE_NAME");
		String columnName = rs.getString("COLUMN_NAME");
		TableMetadata tableMetadata = TableMetadata.find(tableName);
		ColumnMetadata keyColumn = tableMetadata.getColumns()
				.remove(columnName);
		tableMetadata.getKeys().put(columnName,
				PKColumnMetadata.from(keyColumn));
		System.out.println(String.format("  表【%s】中的【%s】列标记为主键", tableName, columnName));
	}

	private static void solveTable(ResultSet rs) throws SQLException {
		TableMetadata table = TableMetadata.find(rs.getString("TABLE_NAME "
				.trim()));
		table.setTableCat(rs.getString("TABLE_CAT  ".trim()));
		table.setTableSchema(rs.getString("TABLE_SCHEM".trim()));
		table.setTableType(rs.getString("TABLE_TYPE ".trim()));
		table.setRemarks(rs.getString("REMARKS    ".trim()));
		System.out.println(String.format("  发现表【%s】", table.getTableName()));
	}

	private static void solveColumn(ResultSet rs) throws SQLException {
		ColumnMetadata column = new ColumnMetadata();
		column.setTableCat(rs.getString("TABLE_CAT         ".trim()));
		column.setTableSchema(rs.getString("TABLE_SCHEM       ".trim()));
		column.setTableName(rs.getString("TABLE_NAME        ".trim()));
		column.setColumnName(rs.getString("COLUMN_NAME       ".trim()));
		column.setDataType(rs.getInt("DATA_TYPE         ".trim()));
		// column.setTypeName (rs.getString("TYPE_NAME         ".trim()));
		column.setTypeName(SqlTypeUtils.decodeToName(column.getDataType()));
		column.setColumnSize(rs.getInt("COLUMN_SIZE       ".trim()));
		column.setDecimalDigits(rs.getInt("DECIMAL_DIGITS    ".trim()));
		column.setNumPrecRadix(rs.getInt("NUM_PREC_RADIX    ".trim()));
		column.setNullable(rs.getInt("NULLABLE          ".trim()));
		column.setRemarks(rs.getString("REMARKS           ".trim()));
		column.setColumnDef(rs.getString("COLUMN_DEF        ".trim()));
		column.setCharOctetLength(rs.getInt("CHAR_OCTET_LENGTH ".trim()));
		column.setOrdinalPosition(rs.getInt("ORDINAL_POSITION  ".trim()));
		column.setIsNullable(rs.getString("IS_NULLABLE       ".trim()));
		column.setScopeCatalog(rs.getString("SCOPE_CATALOG     ".trim()));
		column.setScopeSchema(rs.getString("SCOPE_SCHEMA      ".trim()));
		column.setScopeTable(rs.getString("SCOPE_TABLE       ".trim()));
		column.setSourceDataType(rs.getShort("SOURCE_DATA_TYPE  ".trim()));
		column.setIsAutoincrement(rs.getString("IS_AUTOINCREMENT  ".trim()));
		TableMetadata targetTable = TableMetadata.find(column.getTableName());
		targetTable.addColumn(column);
		System.out.println(String.format("  表【%s】发现列【%s】，列类型为【%s】",
				column.getTableName(), column.getColumnName(),
				column.getTypeName()));
	}
}
